from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)


class Config:
    '''配置参数'''
    SQLALCHEMY_DATABASE_URI = "mysql://root:123456@localhost:3306/flask_db"
    SQLALCHEMY_ECHO = True
# 将数据库配置匹配到到FLask
app.config.from_object(Config)
# 创建数据库连接对象
db = SQLAlchemy(app)

class Dept(db.Model):
    '''部门表'''
    __tablename__ = "dept"
    id = db.Column(db.Integer,primary_key = True)
    name=db.Column(db.String(32),unique=True)
class Employee(db.Model):
    __tablename__ = "emp"
    id = db.Column(db.Integer, primary_key=True)
    name=db.Column(db.String(20))
    age = db.Column(db.Integer)
    dept_id = db.Column(db.Integer,db.ForeignKey('dept.id'))

    def __repr__(self):
        return f'(id={self.id} name="{self.name}" age={self.age} dept_id={self.dept_id})'

# 增加
def dept_add():
    new_dept = [Dept(name="技术部"),
        Dept(name="市场部"),
        Dept(name="效率部")]

    db.session.add_all(new_dept)
    db.session.commit()
def emp_add():
    new_emp=[
        Employee(name="李伟聪",age=22,dept_id=1),
        Employee(name="张宗杭",age= 22,dept_id= 2),
        Employee(name="王丙银",age= 22,dept_id= 3),
        Employee(name="韩耀祖",age= 22,dept_id= 1)
    ]
    db.session.add_all(new_emp)
    db.session.commit()
def find_all_emp():
    emps=Employee.query.all()
    print(emps)
def find_id_emp(id):
    emp=db.session.get(Employee,id)
    print(emp)
def find_name_emp(name):
    emp=Employee.query.filter(Employee.name==name).all()
    print(emp)
def find_and_emp(name,dept):
    emp=Employee.query.filter(Employee.name==name,Employee.dept_id==dept).all()
    print(emp)
def find_emp_like_name(name):
    emp = Employee.query.filter(Employee.name.like(f"{name}%")).all()
    print(emp)
def update_emp(id,age):
    # Employee.query.get(id).name = name
    db.session.get(Employee,id).age = age
    db.session.commit()
def del_id(id):
    db.session.delete(db.session.get(Employee,id))
    db.session.commit()
if __name__ == '__main__':
    with app.app_context(): #应用上下文
        # dept_add()
        # emp_add()
        # find_all_emp()
        # find_id_emp(2)
        # find_name_emp("李伟聪")
        # find_and_emp("李伟聪",1)
        # find_emp_like_name("李")
        update_emp(2,23)
        # del_id(5)